package com.oyjd.soft;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.oyjd.FileOP;

public class Conn {
	public static String jdbcURL = "jdbc:mysql://127.0.0.1:3306/SLDB";
	public static String jdbcAccount = "root";
	public static String jdbcPwd = "3SmartWay741()<>";

	public static Connection getConn() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(jdbcURL, jdbcAccount, jdbcPwd);
	}

	/**
	 * 执行sql
	 * 
	 * @throws Exception
	 */
	public static void executeSql(String sql) throws Exception {
		Connection conn = null;
		try {
			conn = getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) throws Exception {
		String sql = "select * from T_TEMP_APK_DOWN";
		Connection conn = getConn();
		try {
			// 创建一个jdbc声明
			Statement stmt = conn.createStatement();
			// 执行查询
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String username = rs.getString("user_id");
				String password = rs.getString("name");
				System.out.println(username + "" + password);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally {
			// 预防性关闭连接（避免异常发生时在try语句块关闭连接没有执行)
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
		}
	}

	public static void clearTemp() throws Exception {
		executeSql("delete from T_TEMP_APK_DOWN");
	}

	/**
	 * 插入原始链接数据
	 * 
	 * @param urls
	 * @param type
	 * @throws Exception
	 */
	public static void insertLink(List<String> urls, String type) throws Exception {
		String sql = "insert into T_TEMP_APK_DOWN (ID,TYPE,LINK,LINK_FLAG,POS,DOWN_FLAG) values(?,?,?,?,?,?)";
		Connection conn = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < urls.size(); i++) {
				String url = urls.get(i);
				ps.setString(1, FileOP.getUUID());
				ps.setString(2, type);
				ps.setString(3, url);
				ps.setString(4, "0");
				ps.setInt(5, i + 1);
				ps.setString(6, "0");
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取临时表数据
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<SoftVO> getSoftList(String type) throws Exception {
		List<SoftVO> list = new ArrayList<SoftVO>();
		String ex = "";
		if (null != type) {
			ex = " where type='" + type + "'";
		}
		String sql = "select ID,TYPE,LINK,LINK_FLAG,NAME,AUTHOR,DESCRIPTION,UPDATE_DESC,VER,APPLY_COMP,SCREEN_SHOT1,SCREEN_SHOT2,SCREEN_SHOT3,SCREEN_SHOT4,SCREEN_SHOT5,APK_PATH,ICON_PATH,ORA_ID,DOWN_FLAG,POS from T_TEMP_APK_DOWN " + ex + " order by POS";
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				SoftVO vo = new SoftVO();
				vo.setId(rs.getString("id"));
				vo.setType(rs.getString("type"));
				vo.setLink(rs.getString("link"));
				vo.setLink_flag(rs.getString("link_flag"));
				vo.setName(rs.getString("name"));
				vo.setAuthor(rs.getString("author"));
				vo.setDesc(rs.getString("DESCRIPTION"));
				vo.setUpDesc(rs.getString("UPDATE_DESC"));
				vo.setVer(rs.getString("ver"));
				vo.setApply_comp(rs.getString("apply_comp"));
				vo.setShot1(rs.getString("SCREEN_SHOT1"));
				vo.setShot2(rs.getString("SCREEN_SHOT2"));
				vo.setShot3(rs.getString("SCREEN_SHOT3"));
				vo.setShot4(rs.getString("SCREEN_SHOT4"));
				vo.setShot5(rs.getString("SCREEN_SHOT5"));
				vo.setApk_path(rs.getString("apk_path"));
				vo.setIcon_path(rs.getString("icon_path"));
				vo.setOra_id(rs.getString("ora_id"));
				vo.setDown_flag(rs.getString("down_flag"));
				vo.setPos(rs.getInt("POS"));
				list.add(vo);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 获取新数据中无匹配的剩余旧数据
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<SoftVO> getGameSoftListLeft() throws Exception {
		List<SoftVO> list = new ArrayList<SoftVO>();
		String sql = "select a.SOFT_ID from T_SOFT a,T_SOFT_TYPE_ORDER b where a.soft_id=b.soft_id and b.type='Soft_Game' and not exists(select 1 from T_TEMP_APK_DOWN c where c.type='1' and a.name=c.name)";
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				SoftVO vo = new SoftVO();
				vo.setOra_id(rs.getString("SOFT_ID"));
				list.add(vo);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 获取新数据中无匹配的剩余旧数据
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<SoftVO> getAppSoftListLeft() throws Exception {
		List<SoftVO> list = new ArrayList<SoftVO>();
		String sql = "select a.SOFT_ID from T_SOFT a,T_SOFT_TYPE_ORDER b where a.soft_id=b.soft_id and b.type='Soft_App' and not exists(select 1 from T_TEMP_APK_DOWN c where c.type='2' and a.name=c.name)";
		Connection conn = null;
		try {
			conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				SoftVO vo = new SoftVO();
				vo.setOra_id(rs.getString("SOFT_ID"));
				list.add(vo);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 比较是否已经存在
	 * 
	 * @throws Exception
	 */
	public static void compareSoft() throws Exception {
		String sql = "update T_TEMP_APK_DOWN a,T_SOFT b set a.ORA_ID=b.soft_id,a.LINK_FLAG='2' where a.NAME=b.NAME";
		executeSql(sql);
	}

	/**
	 * 更新临时表下载内容
	 * 
	 * @param vo
	 * @throws Exception
	 */
	public static void updateSoftContent(SoftVO vo) throws Exception {

		String sql = "update  T_TEMP_APK_DOWN set LINK_FLAG='1',NAME=?,AUTHOR=?,DESCRIPTION=?,UPDATE_DESC=?,VER=?,APPLY_COMP=?,SCREEN_SHOT1=?,SCREEN_SHOT2=?,SCREEN_SHOT3=?,SCREEN_SHOT4=?,SCREEN_SHOT5=?,APK_PATH=?,ICON_PATH=? where ID=?";
		Connection conn = null;
		try {
			conn = getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getName());
			ps.setString(2, vo.getAuthor());
			String desc = vo.getDesc();
			if (desc.length() > 2000) {
				desc = desc.substring(0, 2000);
			}
			ps.setString(3, desc);
			String updesc = vo.getUpDesc();
			if (updesc.length() > 500) {
				updesc = updesc.substring(0, 500);
			}
			ps.setString(4, updesc);
			ps.setString(5, vo.getVer());
			ps.setString(6, vo.getApply_comp());
			ps.setString(7, vo.getShot1());
			ps.setString(8, vo.getShot2());
			ps.setString(9, vo.getShot3());
			ps.setString(10, vo.getShot4());
			ps.setString(11, vo.getShot5());
			ps.setString(12, vo.getApk_path());
			ps.setString(13, vo.getIcon_path());
			ps.setString(14, vo.getId());
			ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 合并原始表相关数据
	 * 
	 * @param vo
	 * @throws Exception
	 */
	public static void updateSoftOraId(SoftVO vo) throws Exception {
		String sql = "update  T_TEMP_APK_DOWN set ORA_ID=? where ID=?";
		Connection conn = null;
		try {
			conn = getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getOra_id());
			ps.setString(2, vo.getId());
			ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 下载资源完成后重置标志
	 * 
	 * @param vo
	 * @throws Exception
	 */
	public static void updateSoftDown(SoftVO vo) throws Exception {
		if ("1".equals(vo.getDown_flag())) {
			String sql = "update  T_TEMP_APK_DOWN set DOWN_FLAG='1' where ID=?";
			Connection conn = null;
			try {
				conn = getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, vo.getId());
				ps.executeUpdate();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 插入数据
	 * 
	 * @param urls
	 * @param type
	 * @throws Exception
	 */
	public static void insertApk(List<SoftVO> list, String outPath) throws Exception {
		String sql = "insert into T_SOFT (SOFT_ID, APPLY_COMP, AUTHOR, DESCRIPTION, ICON_PATH, NAME, SOU_PATH, SPACE_SIZE, UPDATE_DESC, VER, SCREEN_SHOT1, SCREEN_SHOT2, SCREEN_SHOT3, SCREEN_SHOT4, SCREEN_SHOT5, STATUS, CREATOR, CREATE_TIME, LAST_EDITOR, LAST_UPDATE_TIME) values ";
		sql += "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 'admin', now(), 'admin', now())";
		Connection conn = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql);
			for (SoftVO vo : list) {
				if ("1".equals(vo.getLink_flag())) {
					ps.setString(1, vo.getId());
					ps.setString(2, vo.getApply_comp());
					ps.setString(3, vo.getAuthor());
					ps.setString(4, vo.getDesc());
					ps.setString(5, FileOP.getNewHashName(vo.getIcon_path(), "sl_icon"));
					ps.setString(6, vo.getName());
					String n = FileOP.getNewHashName(NetDeal.soft_url + vo.getApk_path() + ".apk", "sl_apk");
					ps.setString(7, n);
					ps.setLong(8, new File(outPath + n).length());
					ps.setString(9, vo.getUpDesc());
					ps.setString(10, vo.getVer());
					ps.setString(11, FileOP.getNewHashName(vo.getShot1(), "sl_shotcut"));
					ps.setString(12, FileOP.getNewHashName(vo.getShot2(), "sl_shotcut"));
					ps.setString(13, FileOP.getNewHashName(vo.getShot3(), "sl_shotcut"));
					ps.setString(14, FileOP.getNewHashName(vo.getShot4(), "sl_shotcut"));
					ps.setString(15, FileOP.getNewHashName(vo.getShot5(), "sl_shotcut"));
					ps.addBatch();
					System.out.println("新增软件-------" + ("1".equals(vo.getType()) ? "游戏" : "应用") + vo.getName());
				}
			}
			ps.executeBatch();
			conn.commit();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 插入类型排序
	 * 
	 * @param urls
	 * @param type
	 * @throws Exception
	 */
	public static void insertSort(List<SoftVO> list, String sortKey) throws Exception {
		executeSql("delete from T_SOFT_TYPE_ORDER where TYPE='" + sortKey + "'");

		String sql = "insert into T_SOFT_TYPE_ORDER (ORDER_KEY, SOFT_ID, TYPE, ID) values (?, ?, ?, ?)";
		Connection conn = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql);

			for (int i = 0; i < list.size(); i++) {
				SoftVO vo = list.get(i);
				ps.setInt(1, i + 1);
				if ("2".equals(vo.getLink_flag())) {
					ps.setString(2, vo.getOra_id());
				} else {
					ps.setString(2, vo.getId());
				}
				ps.setString(3, sortKey);
				ps.setString(4, FileOP.getUUID());
				ps.addBatch();
				System.out.println("--------insertSort【" + sortKey + "】");
			}
			ps.executeBatch();
			conn.commit();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
